﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for EtiqaCarMoreDetailDB
/// </summary>
/// 
namespace CE.CarInsurance
{
    public class EtiqaCarMoreDetailDB
    {
        public EtiqaCarMoreDetailDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(EtiqaCarMoreDetail etiqacarmoredetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarMoreDetailInsertUpdateSingleItemEtiqa";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarMoreDetailId", etiqacarmoredetail.CECarMoreDetailId);
            _sPara.Add("@CECarID", etiqacarmoredetail.CECarId);
            _sPara.Add("@ProviderCompanyId", etiqacarmoredetail.ProviderCompanyId);
            _sPara.Add("@Name", etiqacarmoredetail.Name);
            _sPara.Add("@PostalCode", etiqacarmoredetail.PostalCode);
            _sPara.Add("@UnitNo", etiqacarmoredetail.UnitNo);
            _sPara.Add("@HomeNo", etiqacarmoredetail.HomeNo);
            _sPara.Add("@Address", etiqacarmoredetail.Address);
            _sPara.Add("@ContactNo", etiqacarmoredetail.ContactNo);
            _sPara.Add("@NRICNo", etiqacarmoredetail.NRICNo);
            _sPara.Add("@Nationality", etiqacarmoredetail.Nationality);
            _sPara.Add("@VechicleNo", etiqacarmoredetail.VechicleNo);
            _sPara.Add("@EngineNo", etiqacarmoredetail.EngineNo);
            _sPara.Add("@ChassisNo", etiqacarmoredetail.ChassisNo);
            _sPara.Add("@HasHirePurchase", etiqacarmoredetail.HasHirePurchase);
            _sPara.Add("@HPCompanyName", etiqacarmoredetail.HPCompanyName);
            _sPara.Add("@HasPreInsurer", etiqacarmoredetail.HasPreInsurer);
            _sPara.Add("@PreInsurerName", etiqacarmoredetail.PreInsurerName);
            _sPara.Add("@PreInsPolicyNo", etiqacarmoredetail.PreInsPolicyNo);
            _sPara.Add("@PreInsExpiryDate", etiqacarmoredetail.PreInsExpiryDate);
            _sPara.Add("@HasDemeritPoints", etiqacarmoredetail.HasDemeritPoints);
            _sPara.Add("@HasInsuranceTerminated", etiqacarmoredetail.HasInsuranceTerminated);
            _sPara.Add("@InsuranceTerminatedReason", etiqacarmoredetail.InsuranceTerminatedReason);
            _sPara.Add("@HasPhysicalImpairment", etiqacarmoredetail.HasPhysicalImpairment);
            _sPara.Add("@PhysicalImpairmentReason", etiqacarmoredetail.PhysicalImpairmentReason);
            _sPara.Add("@PamentType", etiqacarmoredetail.PamentType);          
            _sPara.Add("@CCHEmail", etiqacarmoredetail.CCHEmail);
            _sPara.Add("@OrderNo", etiqacarmoredetail.OrderNo);
            _sPara.Add("@OrderSerialNo", etiqacarmoredetail.OrderSerialNo);
            _sPara.Add("@PaymentStatus", etiqacarmoredetail.PaymentStatus);
            _sPara.Add("@QuoteRefNo", etiqacarmoredetail.QuoteRefNo);
            _sPara.Add("@RefCEUserId", etiqacarmoredetail.RefCEUserId);
            _sPara.Add("@ViewedDetailId", etiqacarmoredetail.ViewedDetailId);
            _sPara.Add("@Status", etiqacarmoredetail.Status);
            _sPara.Add("@CarResultId", etiqacarmoredetail.CarResultId);
            _sPara.Add("@Info1", etiqacarmoredetail.Info1);
            _sPara.Add("@NInfo1", etiqacarmoredetail.NInfo1);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 UpdateStatus(EtiqaCarMoreDetail etiqacarmoredetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCISelectedItemStatusUpdateSingleItemEtiqa";

            SortedList _sPara = new SortedList();

            #region Parameter
            //_sPara.Add("@CECarID", etiqacarmoredetail.CECarId);
            //_sPara.Add("@CarResultId", etiqacarmoredetail.CarResultId);

            _sPara.Add("@OrderNo", etiqacarmoredetail.OrderNo);
            _sPara.Add("@FilePath", etiqacarmoredetail.Info10);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 InsertUpdateSelectStatus(EtiqaCarMoreDetail etiqacarmoredetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocSelectedCarDetailInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarMoreDetailId", etiqacarmoredetail.CECarMoreDetailId);
            _sPara.Add("@CECarId", etiqacarmoredetail.CECarId);
            _sPara.Add("@CarResultId", etiqacarmoredetail.CarResultId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 UpdateMailSendingStatus(Int64 CECarMoreDetailId)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocProviderCarInsuranceProposalSubmissionFileListUpdate";

            SortedList _sPara = new SortedList();
            _sPara.Add("@CECarMoreDetailId", @CECarMoreDetailId);
            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public bool Update()
        {
            return true;
        }

        public bool Delete()
        {
            return true;
        }

        public EtiqaCarMoreDetail GetCarMoreDetail(Int64 CarId, Int64 CEUserId)
        {
            EtiqaCarMoreDetail _etiqacarmoredetail = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarMoreDetailSelectSingleItemEtiqa";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarID", CarId);
            _spara.Add("@RefCEUserId", CEUserId);

            DataTable _dt = _db.GetDataTableBySP(_spName, _spara);
            if (_dt != null)
            {
                if (_dt.Rows.Count > 0)
                {
                    _etiqacarmoredetail = new EtiqaCarMoreDetail();
                    _etiqacarmoredetail = FillDataRecord(_dt);
                }
            }
            return _etiqacarmoredetail;
        }

        public EtiqaCarMoreDetail GetCarMoreDetail(string OrderNo)
        {
            EtiqaCarMoreDetail _etiqacarmoredetail = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarMoreDetailSelectSingleItemEtiqaByOrderNo";

            SortedList _spara = new SortedList();
            _spara.Add("@OrderNo", OrderNo);

            DataTable _dt = _db.GetDataTableBySP(_spName, _spara);
            if (_dt != null)
            {
                if (_dt.Rows.Count > 0)
                {
                    _etiqacarmoredetail = new EtiqaCarMoreDetail();
                    _etiqacarmoredetail = FillDataRecord(_dt);
                }
            }
            return _etiqacarmoredetail;
        }

        private EtiqaCarMoreDetail FillDataRecord(DataTable myDataTable)
        {
            EtiqaCarMoreDetail myCarMoreDetail = new EtiqaCarMoreDetail();
            myCarMoreDetail.CECarMoreDetailId = Convert.ToInt64(myDataTable.Rows[0]["CECarMoreDetailId"].ToString());
            myCarMoreDetail.CECarId = Convert.ToInt64(myDataTable.Rows[0]["CECarID"].ToString());
            myCarMoreDetail.ProviderCompanyId = Convert.ToInt64(myDataTable.Rows[0]["ProviderCompanyId"].ToString());
            myCarMoreDetail.Name = myDataTable.Rows[0]["Name"].ToString();
            myCarMoreDetail.PostalCode = myDataTable.Rows[0]["PostalCode"].ToString();
            myCarMoreDetail.UnitNo = myDataTable.Rows[0]["UnitNo"].ToString();
            myCarMoreDetail.HomeNo = myDataTable.Rows[0]["HomeNo"].ToString();
            myCarMoreDetail.Address = myDataTable.Rows[0]["Address"].ToString();
            myCarMoreDetail.ContactNo = myDataTable.Rows[0]["ContactNo"].ToString();
            myCarMoreDetail.NRICNo = myDataTable.Rows[0]["NRICNo"].ToString();
            myCarMoreDetail.Nationality = myDataTable.Rows[0]["Nationality"].ToString();
            myCarMoreDetail.VechicleNo = myDataTable.Rows[0]["VechicleNo"].ToString();
            myCarMoreDetail.EngineNo = myDataTable.Rows[0]["EngineNo"].ToString();
            myCarMoreDetail.ChassisNo = myDataTable.Rows[0]["ChassisNo"].ToString();
            myCarMoreDetail.HasHirePurchase = Convert.ToBoolean(myDataTable.Rows[0]["HasHirePurchase"].ToString());
            myCarMoreDetail.HPCompanyName = myDataTable.Rows[0]["HPCompanyName"].ToString();
            myCarMoreDetail.HasPreInsurer = Convert.ToBoolean(myDataTable.Rows[0]["HasPreInsurer"].ToString());
            myCarMoreDetail.PreInsurerName = myDataTable.Rows[0]["PreInsurerName"].ToString();
            myCarMoreDetail.PreInsPolicyNo = myDataTable.Rows[0]["PreInsPolicyNo"].ToString();
            myCarMoreDetail.PreInsExpiryDate = Convert.ToDateTime(myDataTable.Rows[0]["PreInsExpiryDate"].ToString());
            myCarMoreDetail.HasDemeritPoints = Convert.ToBoolean(myDataTable.Rows[0]["HasDemeritPoints"].ToString());
            myCarMoreDetail.HasInsuranceTerminated = Convert.ToBoolean(myDataTable.Rows[0]["HasInsuranceTerminated"].ToString());
            myCarMoreDetail.InsuranceTerminatedReason = myDataTable.Rows[0]["InsuranceTerminatedReason"].ToString();
            myCarMoreDetail.HasPhysicalImpairment = Convert.ToBoolean(myDataTable.Rows[0]["HasPhysicalImpairment"].ToString());
            myCarMoreDetail.PhysicalImpairmentReason = myDataTable.Rows[0]["PhysicalImpairmentReason"].ToString();
            myCarMoreDetail.RowGUID = myDataTable.Rows[0]["RowGUID"].ToString();
            myCarMoreDetail.RefCEUserId = Convert.ToInt64(myDataTable.Rows[0]["RefCEUserId"].ToString());
            myCarMoreDetail.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            return myCarMoreDetail;
        }

        //public DataTable GetProviderCompany(Int64 CarId, Int64 CEId, Int64 CXCoverTypeId, Int64 ProviderCompanyId)
        //{
        //    DataTable _demeritpoints = null;
        //    MyDatabase _db = new MyDatabase();
        //    string _spName = "sprocCarInsuranceSelectedPlanSingleItem";

        //    SortedList _spara = new SortedList();
        //    _spara.Add("@CECarId", CarId);
        //    _spara.Add("@RefCEUserId", CEId);
        //    _spara.Add("@CITempResultId", CXCoverTypeId);
        //    _spara.Add("@ProviderCompanyId", ProviderCompanyId);


        //    using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
        //    {
        //        if (_dt != null && _dt.Rows.Count > 0)
        //        {
        //            _demeritpoints = _dt;
        //        }
        //    }
        //    return _demeritpoints;
        //}

        public DataSet GetCarPolicyDetails(Int64 CarId, Int64 CEId, Int64 CXCoverTypeId, Int64 ProviderCompanyId)
        {
            DataSet _result = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarInsuranceSelectedPlanSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CarId);
            _spara.Add("@RefCEUserId", CEId);
            _spara.Add("@CITempResultId", CXCoverTypeId);
            _spara.Add("@ProviderCompanyId", ProviderCompanyId);


            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables[0].Rows.Count > 0)
                {
                    _result = _ds;
                }
            }
            return _result;
        }

        public DataSet GetPolicyDetails(string OrderNo)
        {
            DataSet _result = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocProposalSelectItemtoPDF_Etiqa";

            SortedList _spara = new SortedList();
            _spara.Add("@OrderNo", OrderNo);

            _result = new DataSet();
            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables[0].Rows.Count > 0)
                {
                    _result = _ds;
                }
            }
            return _result;
        }

        public DataSet GetPremiumDetails(string OrderNo)
        {
            DataSet _result = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocProposalPremiumSelectItemtoPDF_Etiqa";

            SortedList _spara = new SortedList();
            _spara.Add("@OrderNo", OrderNo);

            _result = new DataSet();
            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables[0].Rows.Count > 0)
                {
                    _result = _ds;
                }
            }
            return _result;
        }

    }
}